﻿// --------------------------------------------------------------------------------------------------------------------
// <summary>
//   SQLQueryStep.cs
// </summary>
// --------------------------------------------------------------------------------------------------------------------
namespace TRlib.BizUnit.CustomSteps
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Globalization;
    using System.Text;
    using System.Threading;
    using System.Xml;
    using global::BizUnit;

    /// <summary>
    /// The SQLQueryStep runs a SQL query and validates content and rowCount returned from database
    /// </summary>
    /// <remarks>
    /// Usage:
    /// <TestStep assemblyPath="" typeName="SqlQueryStep">
    /// 
    ///   <SQLQuery>
    ///     <RawSQLQuery>
    ///       select Token, Column1 from dbo.UserToken where x = '{0}' and y = '{1}'
    ///     </RawSQLQuery>
    ///     <SQLQueryParams>
    ///        <SQLQueryParam>AValue</SQLQueryParams>
    ///        <SQLQueryParam takeFromCtx="Value2" />
    ///     </SQLQueryParams>
    ///   </SQLQuery>
    /// 
    ///   <ExpectedRowCount>1</ExpectedRowCount>
    ///   <ConnectionString>Persist Security Info=False;Integrated Security=SSPI;database=rms_web;server=UK00658\RMS;Connect Timeout=10</ConnectionString>
    ///   <Retry>
    ///     <RetryAttempts>30</RetryAttempts>
    ///     <RetryIntervalMs>1000</RetryIntervalMs>
    ///   </Retry>
    ///   <Rows>
    ///     <Row>
    ///       <Columns>
    ///         <Token order="0" type="string" addToCtx="token" />
    ///         <Column1 order="1" type="int">6</Column1>
    ///       </Columns>
    ///     </Row>
    ///   </Rows>
    /// </TestStep>
    /// 
    /// <list type="table">
    ///        <listheader>
    ///            <term>Tag</term>
    ///            <description>Description</description>
    ///        </listheader>
    ///        <item>
    ///            <term>SQLQuery</term>
    ///            <description>Container for SQL query information. </description>
    ///        </item>
    ///        <item>
    ///            <term>RawSQLQuery</term>
    ///            <description>Can contain multiple sql statements separated by colon. Placeholders in the format {0},{1} etc can be used inline</description>
    ///        </item>
    ///        <item>
    ///            <term>SQLQueryParams</term>
    ///            <description>Container for sql paramters</description>
    ///        </item>
    ///        <item>
    ///            <term>SQLQueryParam</term>
    ///            <description>Value to replace with the in-line placeholder(s) in the sql query. Can optionally take the value from context.</description>
    ///        </item>
    ///        <item>
    ///            <term>ExpectedRowCount</term>
    ///            <description>Number of rows expected</description>
    ///        </item>
    ///        <item>
    ///            <term>ConnectionString</term>
    ///            <description>Duh</description>
    ///        </item>
    ///        <item>
    ///            <term>Retry/RetryAttempts</term>
    ///            <description>Number of retry attempts allowed</description>
    ///        </item>
    ///        <item>
    ///            <term>Retry/RetryIntervalMs</term>
    ///            <description>Interval of retries in miliseconds.</description>
    ///        </item>
    ///        <item>
    ///            <term>Rows</term>
    ///            <description>Container for the rows returned from the db</description>
    ///        </item>
    ///        <item>
    ///            <term>Rows/Row</term>
    ///            <description>A single expected row</description>
    ///        </item>
    ///        <item>
    ///            <term>Rows/Row/XXXX</term>
    ///            <description>Name of column expected and value expected (if you need to check value)</description>
    ///        </item>
    ///        <item>
    ///            <term>Rows/Row/XXXX/@order</term>
    ///            <description>The ordinal of the column in the returned dataset</description>
    ///        </item>
    ///        <item>
    ///            <term>Rows/Row/XXXX/@type</term>
    ///            <description>The data type of the column. Limited to string, decimal, int, date</description>
    ///        </item>
    ///        <item>
    ///            <term>Rows/Row/XXXX/@addToCtx</term>
    ///            <description>The context key name if you wish to add this value to the context</description>
    ///        </item>
    /// </list>
    /// 
    /// </remarks>
    public class SqlQueryStep : ITestStep
    {
        #region ITestStep Members
        public void Execute(System.Xml.XmlNode config, Context context)
        {
            // See if connection string is being set in app.config file
            var connectionString = String.Empty;
            if (config.SelectSingleNode("ConnectionStringName") != null)
            {
                // Set connection string from specified name in the test step
                var connectionStringAppSettingName = context.ReadConfigAsString(config, "ConnectionStringName");
                connectionString = System.Configuration.ConfigurationManager.AppSettings[connectionStringAppSettingName];
            }
            else if (config.SelectSingleNode("ConnectionString") != null)
            {
                // Otherwise this is set locally
                connectionString = context.ReadConfigAsString(config, "ConnectionString");
            }

            context.LogInfo("Using database connection string: {0}", connectionString);

            var rowCollection = config.SelectSingleNode("Rows");

            var sqlQuery = this.BuildSQLQuery(config.SelectSingleNode("SQLQuery"), context);

            var expectedRowCount = context.ReadConfigAsInt32(config, "ExpectedRowCount");

            // Retry
            var retryAttempts = 0;
            var retryDelay = 0;
            if (config.SelectSingleNode("Retry") != null)
            {
                retryAttempts = context.ReadConfigAsInt32(config, "Retry/RetryAttempts");
                retryDelay = context.ReadConfigAsInt32(config, "Retry/RetryIntervalMs");
            }

            using (var con = new SqlConnection(connectionString))
            {
                con.Open();

                // Execute the test
                this.Execute(context, rowCollection, con, sqlQuery, expectedRowCount, retryAttempts, retryDelay);
            }

            context.LogInfo("SqlQueryStep executed successfully");
        }
        #endregion

        #region private methods

        private static bool ValidateLong(Context context, string expectedColValue, string colValue, int rowNumber, string colName)
        {
            const string ColType = "long";
            bool success = false;
            long expected = 0;
            long actual = 0;

            if (!Int64.TryParse(expectedColValue.Trim(), out expected))
            {
                try
                {
                    expected = Convert.ToInt64(Convert.ToDecimal(expectedColValue.Trim(), CultureInfo.InvariantCulture));
                }
                catch
                {
                    throw new CustomBizUnitExecutionException(String.Format(CultureInfo.CurrentCulture, "Column {0}, value {1} could not be converted to {2}.", colName, expectedColValue.Trim(), ColType));
                }
            }

            if (!Int64.TryParse(colValue.Trim(), out actual))
            {
                try
                {
                    actual = Convert.ToInt64(Convert.ToDecimal(colValue.Trim(), CultureInfo.InvariantCulture));
                }
                catch
                {
                    throw new CustomBizUnitExecutionException(String.Format(CultureInfo.CurrentCulture, "Column {0}, value {1} could not be converted to {2}.", colName, colValue.Trim(), ColType));
                }
            }

            if (expected == actual)
            {
                success = true;
                context.LogInfo(String.Format(CultureInfo.CurrentCulture, "Expected long returned from database:Row {0}, {1} = {2}", rowNumber, colName, colValue.Trim()));
            }

            return success;
        }

        private void Execute(Context context, XmlNode rowCollection, SqlConnection con, string sqlQuery, int expectedRowCount, int retryAttempts, int retryDelay)
        {
            // Set retry flag on if retry attempts are > 0. 
            bool retry = retryAttempts > 0;
            int tryCount = 0;

            // tryCount always zero on first run
            while (retry || tryCount == 0)
            {
                // Reset rowCount
                int rowCount = 0;

                // Increment try-count
                tryCount++;

                // Retry scope - inside here any exceptions will be silent as long as there are retries pending.
                try
                {
                    using (SqlCommand com = con.CreateCommand())
                    {
                        com.CommandType = System.Data.CommandType.Text;
                        com.CommandText = sqlQuery;

                        context.LogInfo("Executing database query: {0}", sqlQuery);

                        using (var reader = com.ExecuteReader())
                        {
                            // Are there expected rows?
                            if (expectedRowCount > 0)
                            {
                                // Has returned data?
                                if (!reader.HasRows)
                                {
                                    // Reader has no rows so throw exception
                                    throw new CustomBizUnitExecutionException(String.Format("Expected {0} rows, but was 0", expectedRowCount));
                                }

                                // Get data from reader
                                while (reader.Read())
                                {
                                    // Increment actual rowcount
                                    rowCount++;
                                    context.LogInfo(String.Format("Processing Row {0} on attempt {1}:", rowCount, tryCount));

                                    // Are rows specified? Then validate data.
                                    // If not then the user just wants to verify rowcount is correct
                                    if (rowCollection != null)
                                    {
                                        XmlNode rowNode = rowCollection.SelectSingleNode("Row[" + rowCount + "]");

                                        // For each column defined in the row
                                        foreach (XmlNode colNode in rowNode.SelectSingleNode("Columns").ChildNodes)
                                        {
                                            this.ProcessRow(context, colNode, reader, rowCount);
                                        }
                                    }
                                } // While reader.Read

                                // Verify actual rowcount = expected
                                if (rowCount != expectedRowCount)
                                {
                                    // Nope.
                                    throw new CustomBizUnitExecutionException(String.Format("Expected {0} rows, but was {1}", expectedRowCount, rowCount));
                                }
                            } // expectedRowCount > 0
                        } // using data reader

                        // If no exception thrown by this point then has been successful. So set retry = false
                        retry = false;
                        context.LogInfo(String.Format("Validation was successful. Number of rows returned from database = {0}", rowCount));
                    } // using SqlCommand
                }
                catch (Exception)
                {
                    // Work out whether to suppress exception for another retry...
                    if (tryCount >= retryAttempts)
                    {
                        // If try-count >= retry attempts - throw to caller
                        throw;
                    }

                    // Otherwise supress exception and delay before continuing
                    context.LogInfo(String.Format("Retry attempt {0} of {1}", tryCount, retryAttempts));
                    Thread.Sleep(retryDelay);
                } // catch
            } // while retry or trycount = 0
        }

        private void ValidateResult(Context context, string expectedColValue, string colValue, string type, int rowNumber, string colName)
        {
            bool success = false;

            if (type == "string")
            {
                if (expectedColValue.Trim() == colValue.Trim())
                {
                    success = true;
                    context.LogInfo(String.Format("Expected string returned from database:Row {0}, {1} = {2}", rowNumber, colName, colValue.Trim()));
                }
            }
            else if (type == "decimal")
            {
                decimal expected = 0;
                decimal actual = 0;

                if (!Decimal.TryParse(expectedColValue.Trim(), out expected))
                {
                    try
                    {
                        expected = Convert.ToDecimal(expectedColValue.Trim());
                    }
                    catch
                    {
                        throw new CustomBizUnitExecutionException(String.Format("Column {0}, value {1} could not be converted to decimal.", colName, expectedColValue.Trim()));
                    }
                }

                if (!Decimal.TryParse(colValue.Trim(), out actual))
                {
                    try
                    {
                        actual = Convert.ToDecimal(colValue.Trim());
                    }
                    catch
                    {
                        throw new CustomBizUnitExecutionException(String.Format("Column {0}, value {1} could not be converted to decimal.", colName, colValue.Trim()));
                    }
                }

                if (Decimal.Round(expected, 8) == Decimal.Round(actual, 8))
                {
                    success = true;
                    context.LogInfo(String.Format("Expected decimal returned from database:Row {0}, {1} = {2}", rowNumber, colName, colValue.Trim()));
                }
            }
            else if (type == "int")
            {
                int expected = 0;
                int actual = 0;

                if (!Int32.TryParse(expectedColValue.Trim(), out expected))
                {
                    try
                    {
                        expected = Convert.ToInt32(Convert.ToDecimal(expectedColValue.Trim()));
                    }
                    catch
                    {
                        throw new CustomBizUnitExecutionException(String.Format("Column {0}, value {1} could not be converted to int.", colName, expectedColValue.Trim()));
                    }
                }

                if (!Int32.TryParse(colValue.Trim(), out actual))
                {
                    try
                    {
                        actual = Convert.ToInt32(Convert.ToDecimal(colValue.Trim()));
                    }
                    catch
                    {
                        throw new CustomBizUnitExecutionException(String.Format("Column {0}, value {1} could not be converted to int.", colName, colValue.Trim()));
                    }
                }

                if (expected == actual)
                {
                    success = true;
                    context.LogInfo(String.Format("Expected integer returned from database:Row {0}, {1} = {2}", rowNumber, colName, colValue.Trim()));
                }
            }
            else if (type == "long")
            {
                success = ValidateLong(context, expectedColValue, colValue, rowNumber, colName);
            }
            else if (type == "date")
            {
                DateTime expected;
                DateTime actual;

                if (!DateTime.TryParse(expectedColValue.Trim(), out expected))
                {
                    throw new CustomBizUnitExecutionException(String.Format("{0} could not be converted to int.", expectedColValue.Trim()));
                }

                if (!DateTime.TryParse(colValue.Trim(), out actual))
                {
                    throw new CustomBizUnitExecutionException(String.Format("{0} could not be converted to int.", colValue.Trim()));
                }

                if (expected.CompareTo(actual) == 0)
                {
                    success = true;
                    context.LogInfo(String.Format("Expected date returned from database:Row{0}, {1} = {2}", rowNumber, colName, colValue.Trim()));
                }
            }
            else
            {
                throw new CustomBizUnitExecutionException(String.Format("{0} is not a recognised type. Please use string, decimal, date, or int.", type));
            }

            if (!success)
            {
                throw new CustomBizUnitExecutionException(String.Format("Expected value {0}, but was {1} for column {2}, row number {3}. Expected type was {4}", expectedColValue.Trim(), colValue.Trim(), colName.Trim(), rowNumber, type));
            }
        }

        private string BuildSQLQuery(XmlNode queryConfig, Context context)
        {
            string rawSQLQuery = context.ReadConfigAsString(queryConfig, "RawSQLQuery");

            XmlNodeList sqlParams = queryConfig.SelectNodes("SQLQueryParams/*");

            if (null != sqlParams)
            {
                ArrayList paramArray = new ArrayList();

                foreach (XmlNode sqlParam in sqlParams)
                {
                    string p = context.ReadConfigAsString(sqlParam, ".");
                    paramArray.Add(p);
                }

                object[] paramObjs = (object[])paramArray.ToArray(typeof(object));
                return String.Format(rawSQLQuery, paramObjs);
            }
            else
            {
                return rawSQLQuery;
            }
        }

        private void ProcessRow(Context context, XmlNode colNode, SqlDataReader reader, int rowNum)
        {
            // Skip comments
            if (colNode.NodeType == XmlNodeType.Comment)
            {
                return;
            }

            int order = Int32.Parse(colNode.Attributes["order"].Value);
            string colValue = reader.GetSqlValue(order).ToString();
            string expectedColValue = string.Empty;
            string colName = colNode.Name;
            string dataType = colNode.Attributes["type"].Value;

            // addToCtx implementation
            if (colNode.Attributes["addToCtx"] != null)
            {
                // Add value to context
                string ctxValue = colNode.Attributes["addToCtx"].Value;
                context.Add(ctxValue, colValue, true);
                context.LogInfo(String.Format("Adding Key {0} to Context, Value is {1}", ctxValue, colValue));
                return;
            }

            // takeFromCtx implementation
            if (colNode.Attributes["takeFromCtx"] != null)
            {
                // Take value from context
                string ctxValue = colNode.Attributes["takeFromCtx"].Value;
                expectedColValue = context.GetValue(ctxValue);
                context.LogInfo(String.Format("Loaded Key {0} from Context, value is {1}", ctxValue, expectedColValue));
            }
            else if (colNode.InnerText != string.Empty)
            {
                expectedColValue = colNode.InnerText;
            }

            this.ValidateResult(context, expectedColValue, colValue, dataType, rowNum, colName);
        }
        #endregion
    }
}
